
--check branch


select 
c.BRANCH_NAME_SME, c.REGION, count(ActionLogId) as no_log, max(Datetime) as last_login from TBL_Action_Log a, TBL_HR b, TBL_BRANCH c
where a.Username = b.USER_NAME and b.BRANCH_CODE_SME =c.BRANCH_CODE_SME
group by c.BRANCH_NAME_SME, c.REGION
order by count(ActionLogId) desc

select count(ActionLogId) from TBL_Action_Log
----check by user
select 
b.dao,a.Username, b.DAO_NAME as [Full name], 
c.BRANCH_NAME_SME, c.REGION, count(ActionLogId) as no_log, max(Datetime) as last_login from TBL_Action_Log a, TBL_HR b, TBL_BRANCH c
where a.Username = b.USER_NAME and b.BRANCH_CODE_SME =c.BRANCH_CODE_SME
group by c.BRANCH_NAME_SME, c.REGION
, a.Username, b.DAO_NAME, b.dao
order by max(Datetime) desc

----check by function

SELECT B.OBJECT_NAME_LOCAL AS FUNCTION_NAME, B.OBJECT_NAME_ENGLISH AS FUNCTION_ENGLISH_NAME,A.ACTION,A.CONTROLLER, B.OLEVEL, COUNT(ACTIONLOGID) AS NO_LOG
 FROM TBL_ACTION_LOG A, APPLICATION_OBJECT B
WHERE A.ACTION = B.OBJECT_ACTION AND A.CONTROLLER = B.OBJECT_CONTROLLER
 GROUP BY B.OBJECT_NAME_LOCAL, B.OBJECT_NAME_ENGLISH,A.ACTION,A.CONTROLLER, B.OLEVEL
 ORDER BY COUNT(ACTIONLOGID) DESC

 select * from APPLICATION_OBJECT where OBJECT_ACTION = 'TotalCustomer'


 select distinct username, b.* from server12.[SMECustomer360].dbo.tbl_action_log a
 left join TBL_HR b
 on a.username = b.USER_NAME
 where action like '%export%'
--delete from server12.[SMECustomer360].dbo.TBL_Action_Log where Username like '%test%'

select 
c.REGION, count(ActionLogId) as no_log, max(Datetime) as last_login from TBL_Action_Log a, TBL_HR b, TBL_BRANCH c
where a.Username = b.USER_NAME and b.BRANCH_CODE_SME =c.BRANCH_CODE_SME
group by c.REGION
order by count(ActionLogId) desc

select * from tbl_hr where USER_NAME like '%test%'

select count(cif),b.BRANCH_NAME_SME, b.REGION
from TBL_CUSTOMER a, TBL_BRANCH b, tbl_hr c
where a.DAO = c.DAO and a.BRANCH_ID = b.BRANCH_ID and c.BRANCH_CODE_SME <> b.BRANCH_CODE_SME
group by b.BRANCH_NAME_SME,b.REGION
order by b.BRANCH_NAME_SME

select * from tbl_hr where group_id in (3,4)